﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class AssertionType
    {
        public AssertionType()
        {

        }

        public static Guid CreateAssertionType(DTO.AssertionType assertionType, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter SchemaObjectID = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter AssertionImplTypeParam = new SqlParameter("@AssertionImplType", SqlDbType.NVarChar, 256);                    
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Int);                    
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Int);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);
                    SqlParameter MinExpirationDateParam = new SqlParameter("@MinExpirationDate", SqlDbType.DateTime);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = assertionType.Name;
                    DescriptionParam.Value = assertionType.Description;
                    SchemaObjectID.Value = assertionType.SchemaObject.ID;
                    AssertionImplTypeParam.Value = assertionType.AssertionImplementationType;

                    if (assertionType.AvgResponseTimeMS != 0)
                    {
                        AvgResponseTimeMSParam.Value = assertionType.AvgResponseTimeMS;
                    }
                    else
                    {
                        AvgResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (assertionType.MaxResponseTimeMS != 0)
                    {
                        MaxResponseTimeMSParam.Value = assertionType.MaxResponseTimeMS;
                    }
                    else
                    {
                        MaxResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (assertionType.Availability != 0)
                    {
                        AvailabilityParam.Value = assertionType.Availability;
                    }
                    else
                    {
                        AvailabilityParam.Value = DBNull.Value;
                    }

                    if (assertionType.ConcurrentUsers != 0)
                    {
                        ConcurrentUsersParam.Value = assertionType.ConcurrentUsers;
                    }
                    else
                    {
                        ConcurrentUsersParam.Value = DBNull.Value;
                    }

                    if (assertionType.MinExpirationDate != null && DateTime.Compare(assertionType.MinExpirationDate, DateTime.Now) > 0)
                    {
                        MinExpirationDateParam.Value = assertionType.MinExpirationDate;
                    }
                    else
                    {
                        MinExpirationDateParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(SchemaObjectID);
                    cmd.Parameters.Add(AssertionImplTypeParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);                    
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(AvailabilityParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);
                    cmd.Parameters.Add(MinExpirationDateParam);

                    cmd.ExecuteNonQuery();
                    assertionType.ID = (Guid)IDParam.Value;
                    
                    // Assign Subjects
                    if (assertionType.AssertionTypeSubjects != null)
                    {
                        foreach (DTO.EntityClass ec in assertionType.AssertionTypeSubjects)
                        {
                            AssignSubjectToAssertionType(ec, assertionType.ID,txn, conn);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return assertionType.ID;
        }

        public static void DeleteAssertionType(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.AssertionType UpdateAssertionType(DTO.AssertionType assertionType, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter SchemaObjectID = new SqlParameter("@SchemaObjectID", SqlDbType.UniqueIdentifier);
                    SqlParameter AssertionImplTypeParam = new SqlParameter("@AssertionImplType", SqlDbType.NVarChar, 256);
                    SqlParameter AvgResponseTimeMSParam = new SqlParameter("@AvgResponseTimeMS", SqlDbType.Int);
                    SqlParameter MaxResponseTimeMSParam = new SqlParameter("@MaxResponseTimeMS", SqlDbType.Int);
                    SqlParameter AvailabilityParam = new SqlParameter("@Availability", SqlDbType.Decimal);
                    SqlParameter ConcurrentUsersParam = new SqlParameter("@ConcurrentUsers", SqlDbType.Int);
                    SqlParameter MinExpirationDateParam = new SqlParameter("@MinExpirationDate", SqlDbType.DateTime);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = assertionType.ID;
                    NameParam.Value = assertionType.Name;
                    DescriptionParam.Value = assertionType.Description;
                    SchemaObjectID.Value = assertionType.SchemaObject.ID;
                    AssertionImplTypeParam.Value = assertionType.AssertionImplementationType;

                    if (assertionType.AvgResponseTimeMS != 0)
                    {
                        AvgResponseTimeMSParam.Value = assertionType.AvgResponseTimeMS;
                    }
                    else
                    {
                        AvgResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (assertionType.MaxResponseTimeMS != 0)
                    {
                        MaxResponseTimeMSParam.Value = assertionType.MaxResponseTimeMS;
                    }
                    else
                    {
                        MaxResponseTimeMSParam.Value = DBNull.Value;
                    }

                    if (assertionType.Availability != 0)
                    {
                        AvailabilityParam.Value = assertionType.Availability;
                    }
                    else
                    {
                        AvailabilityParam.Value = DBNull.Value;
                    }

                    if (assertionType.ConcurrentUsers != 0)
                    {
                        ConcurrentUsersParam.Value = assertionType.ConcurrentUsers;
                    }
                    else
                    {
                        ConcurrentUsersParam.Value = DBNull.Value;
                    }

                    if (assertionType.MinExpirationDate != null && DateTime.Compare(assertionType.MinExpirationDate, DateTime.Now) > 0)
                    {
                        MinExpirationDateParam.Value = assertionType.MinExpirationDate;
                    }
                    else
                    {
                        MinExpirationDateParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(SchemaObjectID);
                    cmd.Parameters.Add(AssertionImplTypeParam);
                    cmd.Parameters.Add(AvgResponseTimeMSParam);
                    cmd.Parameters.Add(MaxResponseTimeMSParam);
                    cmd.Parameters.Add(AvailabilityParam);
                    cmd.Parameters.Add(ConcurrentUsersParam);
                    cmd.Parameters.Add(MinExpirationDateParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return assertionType;
        }

        public static DTO.AssertionType GetAssertionType(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.AssertionType assertionType = new DTO.AssertionType();
            const string sprocname = "[ServiceEngine].[GetAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            assertionType.ID = reader.GetGuid(0);
                            assertionType.Name = reader.GetString(1);
                            assertionType.SchemaObject = SchemaObject.GetSchemaObject(reader.GetGuid(2), txn, conn);
                            
                            if (reader.IsDBNull(3))
                                assertionType.AssertionImplementationType = null;
                            else
                                assertionType.AssertionImplementationType = reader.GetString(3);
                                                        
                            if (reader.IsDBNull(4))
                            {
                                assertionType.AvgResponseTimeMS = 0;
                            }
                            else
                            {
                                assertionType.AvgResponseTimeMS = reader.GetInt32(4);
                            }                            
                            if (reader.IsDBNull(5))
                            {
                                assertionType.MaxResponseTimeMS = 0;
                            }
                            else
                            {
                                assertionType.MaxResponseTimeMS = reader.GetInt32(5);
                            }
                            
                            if (reader.IsDBNull(6))
                            {
                                assertionType.ConcurrentUsers = 0;
                            }
                            else
                            {
                                assertionType.ConcurrentUsers = reader.GetInt32(6);
                            }
                            if (reader.IsDBNull(7))
                            {
                                assertionType.Availability = 0;
                            }
                            else
                            {
                                assertionType.Availability = reader.GetDecimal(7);
                            }
                            if (!reader.IsDBNull(8))
                            {
                                assertionType.MinExpirationDate = reader.GetDateTime(8);
                            }                                                                                    

                            if (reader.IsDBNull(9))
                                assertionType.Description = null;
                            else
                                assertionType.Description = reader.GetString(9);

                            assertionType.AssertionTypeSubjects = GetAssertionTypeSubjects(assertionType.ID, txn, conn);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return assertionType;

        }

        public static DTO.AssertionTypeList ListAssertionType(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.AssertionTypeList list = new DTO.AssertionTypeList();
            const string sprocname = "[ServiceEngine].[ListAssertionTypes]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.AssertionTypes = new List<DTO.AssertionTypeListElement>();
                            while (reader.Read())
                            {
                                DTO.AssertionTypeListElement element = new DTO.AssertionTypeListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.AssertionTypes.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.AssertionTypeListElement> ListAssertionTypesByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.AssertionTypeListElement> list = new List<DTO.AssertionTypeListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.AssertionTypeListElement>();
                            while (reader.Read())
                            {
                                DTO.AssertionTypeListElement element = new DTO.AssertionTypeListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return list;

        }

        #region AssertionType Subjects functions
        //TODO these are obsolete. Need to remove them as part of the sprint.

        public static void AssignSubjectToAssertionType(DTO.EntityClass entityClass, Guid AssertionTypeID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignSubjectToAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter AssertionTypeIDParam = new SqlParameter("@AssertionTypeID", SqlDbType.UniqueIdentifier);
                    SqlParameter EntityTypeIDParam = new SqlParameter("@EntityTypeID", SqlDbType.SmallInt);

                    cmd.CommandType = CommandType.StoredProcedure;
                    AssertionTypeIDParam.Value = AssertionTypeID;
                    cmd.Parameters.Add(AssertionTypeIDParam);
                    EntityTypeIDParam.Value = (int)entityClass;
                    cmd.Parameters.Add(EntityTypeIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemoveAllSubjectsFromAssertionType(Guid AssertionTypeID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveAllSubjectsFromAssertionType]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter AssertionTypeIDParam = new SqlParameter("@AssertionTypeID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    AssertionTypeIDParam.Value = AssertionTypeID;
                    cmd.Parameters.Add(AssertionTypeIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static DTO.EntityClass[] GetAssertionTypeSubjects(Guid assertionTypeID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.EntityClass> entityList = null;
            DTO.EntityClass[] arrEntity = null;
            const string sprocname = "[ServiceEngine].[GetAssertionTypeSubjects]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter AssertionTypeIDParam = new SqlParameter("@AssertionTypeID", SqlDbType.UniqueIdentifier);
                    AssertionTypeIDParam.Value = assertionTypeID;
                    cmd.Parameters.Add(AssertionTypeIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            entityList = new List<DTO.EntityClass>();
                            while (reader.Read())
                            {
                                entityList.Add((DTO.EntityClass)reader.GetInt16(1));
                            }
                        }
                    }
                }

                arrEntity = entityList.ToArray();
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
            return arrEntity;
        }

        #endregion

    }
}
